1. Related Art
The present invention relates generally to handling improper table access in a database system, and more specifically relates to a system and method for identifying and fixing programs that improperly access tables and removing extraneous indexes that were created to support improper accesses on tables in an application database system.
2. Technical Field
Indexes are an integral part of how programs access data from a relational database system. In particular, indexes allow a data item in a table row, such as a phone number in a phone listing, to be quickly identified and accessed based on a related field, such as a last name, in the same row. Without the index, each of the rows in the table would have to be searched to find the matching row. Accordingly, indexes greatly reduce the time it takes an application to access data from a relational database.
One of the problems associated with complex database systems is that the same data items may be redundantly stored in multiple tables, and application programs may not be designed to retrieve data from its proper place, which is the table where the application has standard indexes to support efficient table access using an index. If a database system has many custom programs and has been in production for several years, administrators and programmers who are not expert on the correct use of the application data model may have written may programs that do not use the tables according to the data model, and database administrators (DBAs) may have created many indexes that would be extraneous if the application data model had been followed.
For example, an invoice number may exist in an invoice table, as well as a payments table. This redundant data in the data model commonly leads to a situation where a programmer developing a program selects rows using a local predicate on a column (e.g., invoice#) in a table (e.g., payment) where the column is present but not indexed for efficient access. This might be done, for example, when the programmer is looking for information related to the payment associated with an invoice. In a typical scenario, the proper place for applications to retrieve rows using the invoice number in accordance with the established data model would be the invoice table, where the associated payment number may also be found. However, a programmer who does not properly understand the data model may design an application that retrieves rows from the payment table, using local predicates on the invoice# to search the payment table by invoice#.
Because the invoice data is not indexed in the payment table, the application will perform slowly. To address this, the programmer or DBA may create a custom index for the payment table. This index is extraneous to the established data model, if the invoice# can be used to perform an indexed retrieval of a row in invoice table which contains a data item which is the key to the payment table. The problem with creating custom indexes is that they can reduce the overall performance of the database system. When unnecessary indexes are defined on a table, the indexes use additional space, and require additional processing, both of which may ultimately impact processing performance. In practice, a complex data model with redundantly stored items leads to situations where application database systems contain many custom, and extraneous, indexes. Unfortunately, there is no simple way to determine how many custom indexes are required, and how many could have been prevented by using the data model correctly.
Ultimately, the problem of using local predicates on the wrong table results either in extraneous indexes reducing the performance of the database system, or applications that are running too slow because they are accessing un-indexed data. Accordingly, a need exists for a system that can identify and facilitate removal of extraneous indexes and/or a system that can identify applications that access data from the wrong table, because the program retrieves the data using local predicates on columns that are not indexed in the standard data model.